﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports WSFactory

Public Class InventariosDAL
    Public Function registraInventarios(ByVal pInventarios As InventariosBE) As Boolean
        Dim instrSql As String
        Dim oInsParm(0 To 8) As OdbcParameter

        Try

            instrSql = "INSERT INTO ""Inventarios""(""ClaProducto"", ""DesProducto"", ""EntIni"", ""Entradas"", ""IdEmpresa"",""Precio"", ""Salidas"", ""Salini"", ""UniMed"") " & _
                                           "VALUES (?,?,?,?,?,?,?,?);"

            oInsParm(0) = New OdbcParameter("", pInventarios.ClaProducto)
            oInsParm(1) = New OdbcParameter("", pInventarios.DesProducto)
            oInsParm(2) = New OdbcParameter("", pInventarios.EntIni)
            oInsParm(3) = New OdbcParameter("", pInventarios.Entradas)
            oInsParm(4) = New OdbcParameter("", pInventarios.IdEmpresa)
            oInsParm(5) = New OdbcParameter("", pInventarios.Precio)
            oInsParm(6) = New OdbcParameter("", pInventarios.Salidas)
            oInsParm(7) = New OdbcParameter("", pInventarios.Salini)
            oInsParm(8) = New OdbcParameter("", pInventarios.UniMed)
            

            'Crea el objeto connection
            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddRange(oInsParm)
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    If (myComm.ExecuteNonQuery() > 0) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        Finally
            instrSql = Nothing
            oInsParm = Nothing
        End Try
    End Function

    Public Function modificaInventarios(ByVal pInventarios As InventariosBE) As Boolean
        Dim instrSql As String
        Dim oInsParm(0 To 8) As OdbcParameter
        Try
            instrSql = "UPDATE ""Inventarios"" " & _
                                        "SET ""ClaProducto""=?, ""DesProducto""=?, ""EntIni""=?, ""Entradas""=?, ""IdEmpresa""=?,""Precio""=?, ""Salidas""=?, ""Salini""=?, ""UniMed""=? " & _
                                                "WHERE ""IdEmpresa"" = ?;"

            oInsParm(0) = New OdbcParameter("", pInventarios.ClaProducto)
            oInsParm(1) = New OdbcParameter("", pInventarios.DesProducto)
            oInsParm(2) = New OdbcParameter("", pInventarios.EntIni)
            oInsParm(3) = New OdbcParameter("", pInventarios.Entradas)
            oInsParm(4) = New OdbcParameter("", pInventarios.Precio)
            oInsParm(5) = New OdbcParameter("", pInventarios.Salidas)
            oInsParm(6) = New OdbcParameter("", pInventarios.Salini)
            oInsParm(7) = New OdbcParameter("", pInventarios.UniMed)
            oInsParm(8) = New OdbcParameter("", pInventarios.IdEmpresa)

            'Crea el objeto connection
            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddRange(oInsParm)
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    Return True
                End Using
            End Using

        Catch ex As Exception
            Throw
        Finally
            instrSql = Nothing
            oInsParm = Nothing
        End Try
    End Function

    Public Function getInventarios(ByVal pIdEmpr As Integer, ByVal pStrInventarios As String) As List(Of InventariosBE)
        Dim InventariosLs As List(Of InventariosBE) = New List(Of InventariosBE)
        Dim instrSql As String

        Try
            instrSql = "SELECT ""ClaProducto"", ""DesProducto"", ""EntIni"", ""Entradas"", ""IdEmpresa"",""Precio"", ""Salidas"", ""Salini"", ""UniMed"" " & _
                                     "FROM ""Inventarios"" " & _
                                    "WHERE ""IdEmpresa"" = ? " & _
                                        "AND ""ClaProducto"" LIKE '%" & pStrInventarios & "%' ;"

            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As OdbcCommand = New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Add(New OdbcParameter("", pIdEmpr))
                    myConn.Open()
                    Using myDR As OdbcDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Inventarios As InventariosBE = InventariosFact.Build(myDR)
                            If (Not Inventarios Is Nothing) Then
                                InventariosLs.Add(Inventarios)
                            End If
                            Inventarios = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return InventariosLs
        Catch ex As Exception
            Throw
        Finally
            InventariosLs = Nothing
            instrSql = Nothing
        End Try
    End Function
End Class
